<?php

namespace App\Http\Controllers\Mileage;

use App\Http\Controllers\Controller;
use App\Http\Controllers\HelperController;
use App\Imports\Mileage\MileageImport;
use App\Models\Dict;
use App\Models\Mileage\Mileage;
use App\Models\Mileage\MileageDrop;
use Exception;
use Illuminate\Database\Query\Builder;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;
use Maatwebsite\Excel\Facades\Excel;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Spreadsheet;

class DropOffController extends Controller
{
    public function dropOffList(Request $request)
    {
        $params = $request->all();

        if ($params['date'][0] === null && $params['date'][1] === null) {
            $params['date'] = date('d') > 25 ?
                [
                    date('Y-m-26', strtotime('-1 month')),
                    date('Y-m-25'),
                ] :
                [
                    date('Y-m-26', strtotime('-2 month')),
                    date('Y-m-25', strtotime('-1 month')),
                ];
        } else {
            $date = $params['date'];
            $params['date'] = [
                date('Y-m-d', strtotime($date[0])),
                date('Y-m-d', strtotime($date[1])),
            ];
        }
        $data = $this->getMileageList($params)->get()->toArray();

        if ($data) {
            $data = $this->transData($data);
        }

        return response()->formatJson(['gasRecord' => $data, 'date' => $params['date']]);
    }

    public function transData($data)
    {
        foreach ($data as $k => $v) {
            $data[$k]['reason'] = $v['reason'] ? json_decode($v['reason']) : '';
        }

        return $data;
    }

    public function getMileageList($params): Builder
    {
        $query = DB::table('akbus_mileage_drop as c')
            ->leftJoin('akbus_bus_distribution as d', function ($join) {
                $join->on('d.license_plate_num', '=', 'c.bus_num')
                    ->on(DB::raw('to_days(c.date)'), '>=', DB::raw('to_days(d.date)'))
                    ->on(DB::raw('to_days(c.date)'), '<=', DB::raw('to_days(d.end_date)'));
            })
            ->select('c.id', 'c.bus_num', 'd.line', 'c.created_at', 'c.date', 'c.drop_trip', 'c.reason', 'c.remark');
        if (isset($params['license_plate_num'])) {
            $query->where('c.bus_num', 'like', '%'.$params['license_plate_num'].'%');
        }

        if (isset($params['line']) && '-1' !== $params['line']) {
            $query->where('d.line', $params['line']);
        }

        return $query->whereBetween('c.date', [$params['date']])->orderByDesc('c.date');
    }

    /**
     * 导入加气记录
     *
     * @param  Request  $request
     *
     * @return mixed
     * @throws Exception
     */
    public function importDropOff(Request $request)
    {
        $urlParse = parse_url($request->input('url'));
        $excelData = Excel::toArray(new MileageImport, public_path().$urlParse['path']);
        $fullData = $excelData[0];

        // 删除第一行
        array_shift($fullData);

        $fields = [
            'date',
            'line',
            'license_plate_num',
            'drop_off',
            'mark',
        ];
        $unsetKeys = [];
        // 整理数据，填充合并的单元格数据
        foreach ($fullData as $k => $row) {
            if (! array_filter($row)) {
                $unsetKeys[] = $k;
                continue;
            }
            if ($row[0] === null) {
                $fullData[$k][0] = $fullData[$k - 1][0];
            }
        }
        // 删除空数据
        if ($unsetKeys) {
            foreach ($unsetKeys as $v) {
                unset($fullData[$v]);
            }
        }
        // 整理数据，填充合并的单元格数据
        foreach ($fullData as $k => $row) {
            $item = [];
            foreach ($fields as $index => $field) {
                $item[$field] = $row[$index];
            }
            $item['date'] = HelperController::transformDateTime($item['date'], 'Y-m-d');
            $record = Mileage::query()
                ->where('date', $item['date'])
                ->where('line', $item['line'])
                ->where('license_plate_num', $item['license_plate_num'])
                ->first();

            if ($record) {
                $millage = Mileage::query()->find($record->id);
                $millage->update(['drop_off' => $item['drop_off']]);
            }
        }

        return response()->formatJson(true, '导入成功');
    }

    /**
     * 修改车辆信息
     *
     * @param  Request  $request
     *
     * @return mixed
     */
    public function updateDropOff(Request $request)
    {
        $updateData = $request->all();
        foreach ($updateData as $k => $v) {
            $mileage = MileageDrop::query()->find($v['id']);
            $reason = count($v['reason']) > 0 ? json_encode($v['reason']) : '';
            $result = $mileage->update(['drop_trip' => $v['drop_trip'], 'reason' => $reason, 'remark' => $v['remark']]);
        }
        $message = $result ? '修改成功' : '修改失败';

        return response()->formatJson($result, $message);
    }

    /**
     * 获取数据字典
     *
     * @param  Request  $request
     *
     * @return mixed
     */
    public function dictData(Request $request)
    {
        $params = $request->all();

        $result = Dict::getOptionsArrByName($params['title']);
        $data = [];
        foreach ($result as $key => $val) {
            $data[$key]['value'] = $val;
            $data[$key]['label'] = $val;
        }
        return response()->formatJson(['result' => $data]);
    }
    
    /**
     * 导出掉趟
     *
     * @param  Request  $request
     *
     * @throws \PhpOffice\PhpSpreadsheet\Exception
     * @throws \PhpOffice\PhpSpreadsheet\Writer\Exception
     */
    public function exportDropOff(Request $request)
    {
        $params = $request->all();//接收参数
       
        $params['date'] = explode(',', $params['date']);
        
        //获取数据
        if ($params['date'][0] === null && $params['date'][1] === null) {
            $params['date'] = date('d') > 25 ?
                [
                    date('Y-m-26', strtotime('-1 month')),
                    date('Y-m-25'),
                ] :
                [
                    date('Y-m-26', strtotime('-2 month')),
                    date('Y-m-25', strtotime('-1 month')),
                ];
        } else {
            $date = $params['date'];
            $params['date'] = [
                date('Y-m-d', strtotime($date[0])),
                date('Y-m-d', strtotime($date[1])),
            ];
        }
        
        $data = $this->getMileageList($params)->get()->toArray();
        
        if ($data) {
            $data = $this->transData($data);
        }
        
        // 创建一个Spreadsheet对象
        $spreadsheet = new Spreadsheet();
        // 设置文档属性
        $spreadsheet->getProperties()->setCreator('Maarten Balliauw')
            ->setLastModifiedBy('Maarten Balliauw')
            ->setTitle('Office 2007 XLSX Test Document')
            ->setSubject('Office 2007 XLSX Test Document')
            ->setDescription('Test document for Office 2007 XLSX, generated using PHP classes.')
            ->setKeywords('office 2007 openxml php')
            ->setCategory('Test result file');

        $spreadsheet->createSheet();
        $spreadsheet->setActiveSheetIndex(0)
                    ->setCellValue('A1', '安康市公共交通责任有限公司掉趟表')
                    ->setCellValue('A2', '核算时间:'.$params['date'][0].'--'.$params['date'][1])
                    ->setCellValue('A3', '时间')
                    ->setCellValue('B3', '线路')
                    ->setCellValue('C3', '车牌号')
                    ->setCellValue('D3', '掉趟')
                    ->setCellValue('E3', '掉趟原因')
                    ->setCellValue('F3', '备注');
        $num = 4;
        $conut = count($data);
        for ($i=0, $iMax = $conut; $i < $iMax; $i++) {
            $spreadsheet->getActiveSheet()->setCellValue('A' . $num, $data[$i]['date']);
            $spreadsheet->getActiveSheet()->setCellValue('B' . $num, $data[$i]['line']);
            $spreadsheet->getActiveSheet()->setCellValue('C' . $num, $data[$i]['bus_num']);
            $spreadsheet->getActiveSheet()->setCellValue('D' . $num, $data[$i]['drop_trip']);
            $spreadsheet->getActiveSheet()->setCellValue('E' . $num, implode('', $data[$i]['reason']));
            $spreadsheet->getActiveSheet()->setCellValue('F' . $num, $data[$i]['remark']);
            $num++;
        }
        //合计
        $spreadsheet->getActiveSheet()->setCellValue('A' . ($num+1), '合计:');
        $spreadsheet->getActiveSheet()->setCellValue('D'.($num+1), '=SUM('.'D4:D'.$num.')');
        $spreadsheet->getActiveSheet()->getCell('D'.($num+1))->getCalculatedValue();
        // 合并行、列
        $spreadsheet->getActiveSheet()->mergeCells('A1:F1')->mergeCells('A2:F2');
        //  设置宽度为15
        $spreadsheet->getActiveSheet()->getDefaultColumnDimension()->setWidth(15);
        
        // 设置高度
        $spreadsheet->getActiveSheet()->getRowDimension('1')->setRowHeight(38);
        $spreadsheet->getActiveSheet()->getRowDimension('2')->setRowHeight(38);
        //默认行高
        $spreadsheet->getActiveSheet()->getDefaultRowDimension()->setRowHeight(15);
        //居中
        $numberStyleCenter = [
            'alignment' => [
                'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER,
                'vertical'   => \PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER,
            ],
        ];
        //居中并自动换行
        $spreadsheet->getActiveSheet()->getStyle('A1:F'.($num+1))->applyFromArray($numberStyleCenter)->getAlignment()
                ->setWrapText(true);

        

        //设置字体
        $spreadsheet->getActiveSheet()->getStyle('A1')->getFont()->setSize(20);
        $spreadsheet->getActiveSheet()->getStyle('A1')->getFont()->setBold(true);
        $spreadsheet->getActiveSheet()->getStyle('A2:F3')->getFont()->setBold(true);
        
        //加边框
        $styleArray = [
            'borders' => [
                'allBorders' => [
                    'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN,
                   
                ]
            ]
        ];
        
        $spreadsheet->getActiveSheet()->getStyle('A1:F'.($num+1))->applyFromArray($styleArray);
        // 重命名 worksheet
        $c = '掉趟'.$params['date'][0].'-'.$params['date'][1];
        $spreadsheet->getActiveSheet()->setTitle($c);
       
        // 将活动工作表索引设置为第一个工作表，以便Excel将其作为第一个工作表打开
        // 将输出重定向到客户端的Web浏览器 (Xlsx)
        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        header('Content-Disposition: attachment;filename="'.$c.'.xlsx"');
        header('Cache-Control: max-age=0');
        // 如果正在使用IE 9
        header('Cache-Control: max-age=1');
        // If you're serving to IE over SSL, then the following may be needed
        header('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past
        header('Last-Modified: '.gmdate('D, d M Y H:i:s').' GMT'); // always modified
        header('Cache-Control: cache, must-revalidate'); // HTTP/1.1
        header('Pragma: public'); // HTTP/1.0
        $writer = IOFactory::createWriter($spreadsheet, 'Xlsx');
        $writer->save('php://output');
        exit;
    }
}
